﻿use musicmanStaging
go

-- drop table search criteria obsollete
if exists (select * from information_schema.tables where table_name = 'SearchCriteria')
begin
	drop table SearchCriteria
end
go

-- add column search locationID
if not exists (select * from information_schema.columns where table_name = 'SearchFile' and column_name = 'SearchLocationID')
begin
	ALTER table SearchFile add SearchLocationID int null
end
go

-- add column FolderName
if not exists (select * from information_schema.columns where table_name = 'SearchFile' and column_name = 'FolderName')
begin
	ALTER table SearchFile add FolderName nvarchar(300) null
end
go

-- driop constraint search forlder
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SearchFile_SearchFolder]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
	ALTER TABLE [dbo].[SearchFile] DROP CONSTRAINT [FK_SearchFile_SearchFolder]
GO

-- update search location column value 
if exists(select * from information_schema.columns where table_name = 'SearchFile' and column_name = 'SearchFolderID')
begin
	update dbo.SearchFile set SearchLocationID = SearchFolder.SearchLocationID, FolderName = SearchFolder.Name  
		from dbo.SearchFile inner join dbo.SearchFolder on dbo.SearchFile.SearchFolderID = dbo.SearchFolder.SearchFolderID
end
go

-- drop column SearchFolderID
if exists(select * from information_schema.columns where table_name = 'SearchFile' and column_name = 'SearchFolderID')
begin
	ALTER TABLE [dbo].[SearchFile] DROP COLUMN [SearchFolderID]
end
go

-- set search location column to not null
if exists (select * from information_schema.columns where table_name = 'SearchFile' and column_name = 'SearchLocationID')
begin
	ALTER table SearchFile alter column SearchLocationID int not null
end
go

-- set FolderName column to not null
if exists (select * from information_schema.columns where table_name = 'SearchFile' and column_name = 'FolderName')
begin
	ALTER table SearchFile alter column FolderName nvarchar(300) not null
end
go

-- add constraint search forlder
IF  not EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SearchFile_SearchLocation]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
	ALTER TABLE [dbo].[SearchFile]  WITH NOCHECK ADD  CONSTRAINT [FK_SearchFile_SearchLocation] FOREIGN KEY([SearchLocationID])
	REFERENCES [dbo].[SearchLocation] ([SearchLocationID])
GO

-- drop table search forlder 
if exists (select * from information_schema.tables where table_name = 'SearchFolder')
begin
	drop table SearchFolder
end
go

